ALTER FUNCTION [dbo].[CalInterestWkly]
(
	@report_month INTEGER,
	@report_year INTEGER,
	@principal DECIMAL(18, 2),
	@interest_rate DECIMAL(18, 8),
	@loan_start_date DATETIME,	
	@term INTEGER,	
	@payment_amount DECIMAL(18, 2)	
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

DECLARE @interest DECIMAL(18, 8) = 0,
	@wkly_interest DECIMAL(18, 8) = 0,	
	@payment_date DATETIME = DATEADD(wk, 1, @loan_start_date),
	@month_end DATETIME, 
	@counter INTEGER = 1,
	@report_date DATETIME 
	
SET @report_date = dbo.DateFromParts
(
	@report_year, 
	@report_month, 
	DAY(DATEADD(mm, DATEDIFF(mm, -1, dbo.DateFromParts(@report_year, @report_month, 1)), -1))
)	

SET @interest_rate = (@interest_rate / 100) / 52

IF @payment_date < @report_date
BEGIN
	WHILE (YEAR(@payment_date) < @report_year OR (YEAR(@payment_date) = @report_year AND MONTH(@payment_date) < @report_month))
	BEGIN	
		IF @counter <= @term
		BEGIN
			SET @wkly_interest = @principal * @interest_rate
			SET @interest = @interest + @wkly_interest
			SET @principal = @principal + @wkly_interest - @payment_amount
			SET @payment_date = DATEADD(wk, 1, @payment_date)
			SET @counter = @counter + 1
		END	
		ELSE
		BEGIN
			BREAK
		END	
	END	

	SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @payment_date), -1)) - DAY(@payment_date), @payment_date)
	SET @interest = 0

	WHILE @payment_date <= @month_end 
	BEGIN
		IF @counter <= @term
		BEGIN
			SET @wkly_interest = @principal * @interest_rate
			SET @interest = @interest + @wkly_interest
			SET @principal = @principal + @wkly_interest - @payment_amount
			SET @payment_date = DATEADD(wk, 1, @payment_date)
			SET @counter = @counter + 1
		END	
		ELSE
		BEGIN
			BREAK
		END	
	END
END
RETURN @interest
END;

